package com.shire.movienizerdroid.db;

import java.lang.ref.WeakReference;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.shire.movienizerdroid.MovienizerDroidActivity;
import com.shire.movienizerdroid.activity.MDPreferenceActivity;
import com.shire.movienizerdroid.domain.Movie;
import com.shire.movienizerdroid.utils.EmptyCursor;

/**
 * @author yhernandezdiaz
 */
public class DataSource {
	private static final String SQL_GET_MOVIE_MATCHES = "SELECT m.code as _id, m.title as title, m.tagline AS tagline, m.year as year, i.path AS image_path FROM movies m LEFT JOIN images i ON i.movie = m.code AND i.mode=1 INNER JOIN movies_manuals mm ON mm.movie=m.code INNER JOIN manuals ma ON ma.code=mm.ref_code AND ma.reference=8 INNER JOIN data d ON d.movie=m.code INNER JOIN persons pa ON pa.code=d.person INNER JOIN persons pd ON pd.code=d.person WHERE m.in_collection=1 %s GROUP BY m.code ORDER BY m.title";
	private static final String SQL_FETCH_MOVIE = "SELECT m.code, m.title, m.year, m.duration, m.description, m.tagline, m.rating, m.imdb_rating, m.in_collection, m.seen, m.wanted, m.for_sale, mc.site as imdb_site, i.path AS image_path, i.url AS image_url FROM movies m LEFT JOIN images i ON i.movie = m.code AND i.mode=1 LEFT JOIN movies_codes mc ON mc.movie = m.code AND mc.script='imdb' WHERE m.code=?";
	private static final String SQL_GET_MOVIE_MANUALS = "SELECT m.reference, m.name FROM movies_manuals mm INNER JOIN manuals m ON m.code=mm.ref_code WHERE movie=%d";
	private static final String SQL_GET_MOVIE_PERSONS = "SELECT d.mode, p.name FROM data d INNER JOIN persons p ON p.code=d.person WHERE movie=%d";

	private static DataSource INSTANCE = new DataSource();

	public static DataSource getInstance() {
		return INSTANCE;
	}

	private boolean loaded = false;
	private SQLiteDatabase database = null;
	private WeakReference<Cursor> currentMoviesCursor = null;

	private DataSource() {
		super();
	}

	public synchronized boolean ensureLoaded(Context context) {
		final String databasePath = MDPreferenceActivity.getDatabasePath(context);
		if ((!loaded) && (!TextUtils.isEmpty(databasePath))) {
			try {
				Log.d(MovienizerDroidActivity.LOG_TAG, "Loading database: " + databasePath);
				database = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.OPEN_READONLY | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
				loaded = true;
			} catch (SQLException e) {
				Log.e(MovienizerDroidActivity.LOG_TAG, "Exception ocurred!", e);
			}
		}
		return loaded;
	}

	public DataSource close() {
		if (loaded) {
			database.close();
			loaded = false;
		}
		return this;
	}

	@Override
	protected void finalize() throws Throwable {
		close();
		super.finalize();
	}

	private static final Pattern FILTER_PATTERN = Pattern.compile("((?:genre|year|actor|director)):((?:\"[^\"]+\")|(?:[^ ]+))", Pattern.CASE_INSENSITIVE);

	public Cursor getMatches(CharSequence filter) {
		if (loaded) {
			final StringBuilder wsb = new StringBuilder();
			if (null != filter) {
				final Matcher matcher = FILTER_PATTERN.matcher(filter);
				String column;
				String value;
				while (matcher.find()) {
					column = matcher.group(1).toLowerCase();
					value = matcher.group(2);
					if (value.startsWith("\""))
						value = value.substring(1, value.length() - 1);
					if ("year".equals(column))
						wsb.append("AND m.year='" + value + "'");
					else if ("genre".equals(column))
						wsb.append("AND ma.name LIKE '%" + value + "%'");
					else if ("actor".equals(column))
						wsb.append("AND pa.name LIKE '%" + value + "%'");
					else if ("director".equals(column))
						wsb.append("AND pd.name LIKE '%" + value + "%'");
					else
						Log.d(MovienizerDroidActivity.LOG_TAG, "Invalid filter by " + column + " value " + value);
				}
				value = matcher.replaceAll("").trim();
				if (!TextUtils.isEmpty(value))
					wsb.append("AND UPPER(m.title) LIKE '%" + value + "%'");
			}

			final String query = String.format(SQL_GET_MOVIE_MATCHES, wsb.toString());
			final Cursor cursor = database.rawQuery(query, null);
			currentMoviesCursor = new WeakReference<Cursor>(cursor);
			return cursor;
		} else
			return EmptyCursor.INSTANCE;
	}

	public Cursor getMovieManuals(long movieCode) {
		if (loaded)
			return database.rawQuery(String.format(SQL_GET_MOVIE_MANUALS, movieCode), null);
		else
			return EmptyCursor.INSTANCE;
	}

	public Cursor getMoviePersons(long movieCode) {
		if (loaded)
			return database.rawQuery(String.format(SQL_GET_MOVIE_PERSONS, movieCode), null);
		else
			return EmptyCursor.INSTANCE;
	}

	public Movie getMovie(long code) {
		final Cursor cursor = database.rawQuery(SQL_FETCH_MOVIE, new String[] { String.valueOf(code) });
		cursor.moveToFirst();

		final Movie result = new Movie(cursor);

		cursor.close();
		return result;
	}

	public Cursor getCurrentMoviesCursor() {
		try {
			return currentMoviesCursor.get();
		} catch (NullPointerException e) {
			// No cursor fetch yet, so...
			return null;
		}
	}

	public boolean isLoaded() {
		return loaded;
	}

}
